﻿


--EXEC UP_UserGet 'AnuradhaV', 'AnuradhaV'


CREATE PROCEDURE [dbo].[UP_UserGet]
(
	@LoginName VARCHAR(250),
	@Password VARCHAR(250)
)
AS
BEGIN
	DECLARE @UserUniqueID INT
	
	SELECT @UserUniqueID = UserUniqueKey FROM [User] WHERE LoginName = @LoginName AND ISNULL(IsHidden,0) = 0
	
	SELECT	 UserID
			,UserUniqueKey
			,LoginName
			,UserName
			,[Password]
			,LastLoginTime
	FROM	[User]
	WHERE	UserUniqueKey = @UserUniqueID
			
	SELECT	RL.RoleUniqueKey,
			RL.RoleName 
	FROM	UserRoleMap URM 
			INNER JOIN [Role] RL ON URM.RoleUniqueKey = RL.RoleUniqueKey 
	WHERE   URM.UserUniqueKey = @UserUniqueID 
			AND ISNULL(RL.IsHidden,0) = 0
	
	
	SELECT	RL.RoleUniqueKey,
			RG.RegionUniqueKey,
			RG.RegionName
	FROM	RegionRoleMap RRM 
			INNER JOIN Region RG ON RG.RegionUniqueKey = RRM.RegionUniqueKey
			INNER JOIN [Role] RL ON RL.RoleUniqueKey = RRM.RoleUniqueKey
			INNER JOIN UserRoleMap URM ON URM.RoleUniqueKey = RL.RoleUniqueKey
	WHERE	URM.UserUniqueKey = @UserUniqueID
			AND ISNULL(RG.IsHidden,0) = 0
			AND ISNULL(RL.IsHidden,0) = 0
		
END